﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

using Excel = Microsoft.Office.Interop.Excel;
using System.Data.OleDb;

namespace ExcelToOracle
{
    public class ExcelUtil
    {
        public static Dictionary<string, DataTable> s_mappingValues = new Dictionary<string,DataTable>();
     
        private Excel.Application _app;
        Excel.Workbook _workbooks;
        private string _path;
        public ExcelUtil(string path)
        {
            _path = path;
            _app = new Excel.Application();
            _app.Visible = false;
            object objOpt = System.Reflection.Missing.Value;
            _workbooks = (Excel.Workbook)_app.Workbooks.Open(path, objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);
        }

        public List<string> GetSheets()
        {
            List<string> list = new List<string>();
            Excel.Sheets sheets = _workbooks.Worksheets;
            string sheetNams = string.Empty;
            foreach (Excel.Worksheet sheet in sheets)
            {
                list.Add(sheet.Name);
            }
            return list;
        }

        public DataTable GetData(string sheetName, ImportTemplate template, ConstantValues constantValues)
        {
            DataSet ds;
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=No;IMEX=1';data source=" + _path;
            OleDbConnection myConn = new OleDbConnection(strCon);
            string strCom = " SELECT * FROM [" + sheetName + "$]";
            myConn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
            ds = new DataSet();
            myCommand.Fill(ds);
            myConn.Close();

            DataTable table = ds.Tables[0];

            Dictionary<string, object> constantDict = new Dictionary<string, object>();
            //foreach (string contant in template.Constants)
            //{
            //    constantDict.Add(contant, null);
            //}

            DataTable returnTable = new DataTable();


            #region 添加常量字段
            foreach (FieldTplt constField in template.Constants)
            {
                returnTable.Columns.Add(constField.TableColumn);
            }
            #endregion

            #region 添加需要的字段
            for (int index =table.Columns.Count - 1;index>=0; index--)
            {
                DataColumn column = table.Columns[index];
                string dbColumnName =string.Empty;
                bool isExist = false;
                foreach (FieldTplt tplt in template.Fields)
                {
                    if (Cmp(column.ColumnName, tplt.ExcelColumn))
                    {
                        dbColumnName = tplt.TableColumn;
                        isExist = true;
                        break;
                    }
                }
                if (isExist)
                {
                    returnTable.Columns.Add(dbColumnName);
                    column.ColumnName = dbColumnName;

                }
            }
            #endregion
            

            #region 复制数据
            foreach (DataRow row in table.Rows)
            {
                DataRow newRow = returnTable.NewRow();
                foreach (FieldTplt tplt in template.Fields)
                {
                    if (tplt.Type == "mapping")
                    {
                        newRow[tplt.TableColumn] = this.GetMappingValue(tplt, row[tplt.TableColumn].ToString());
                    }
                    else
                    {
                        if (row[tplt.TableColumn].GetType() == typeof(double))
                        {
                            newRow[tplt.TableColumn] = Math.Round((double)row[tplt.TableColumn], 2);
                        }
                        else
                        {
                            newRow[tplt.TableColumn] = row[tplt.TableColumn];
                        }
                    }
                }
                foreach (FieldTplt constField in template.Constants)
                {
                    foreach (ConstantValue value in constantValues.Values)
                    {
                        if (value.Field == constField)
                        {
                            if (value.Field.Type == "guid")
                            {
                                newRow[constField.TableColumn] = Guid.NewGuid().ToString("D");
                            }
                            else
                            {
                                newRow[constField.TableColumn] = value.Value;
                            }
                            break;
                        }
                    }
                }
                returnTable.Rows.Add(newRow);
            }
            #endregion

            return returnTable;
        }

        public DataTable GetData(string sheetName)
        {
            DataSet ds;
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=No;';data source=" + _path;
            OleDbConnection myConn = new OleDbConnection(strCon);
            string strCom = " SELECT * FROM [" + sheetName + "$]";
            myConn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
            ds = new DataSet();
            myCommand.Fill(ds);
            myConn.Close();

            DataTable table = ds.Tables[0];
            return table;
        }

        public DataTable GetData(string sheetName, int count)
        {
            DataSet ds;
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=No;';data source=" + _path;
            OleDbConnection myConn = new OleDbConnection(strCon);
            string strCom = " SELECT top " + count.ToString() + " * FROM [" + sheetName + "$]";
            myConn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
            ds = new DataSet();
            myCommand.Fill(ds);
            myConn.Close();

            DataTable table = ds.Tables[0];
            return table;
        }

        //public DataTable GetSchema()
        //{
        //    string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=No;';data source=" + _path;
        //    OleDbConnection myConn = new OleDbConnection(strCon);
        //    myConn.Open();

        //    DataTable table = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[]{null,null,"2007$","COLUMN"});
        //    myConn.Close();

        //    return table;
        //}

        public void Close()
        {
            if (_workbooks != null)
            {
                _workbooks.Close(false, Type.Missing, Type.Missing);
                _app.Quit();
            }
        }

        private bool Cmp(string dataTableColumnName, string xmlExcelColumnName)
        {
            int value = Convert.ToInt32(dataTableColumnName.Replace("F", string.Empty));
            int xmlValue = 0;
            for (int index = 0; index < xmlExcelColumnName.Length; index++)
            {
                int c = xmlExcelColumnName[index] - 'A' + 1;

                xmlValue += c * (int)Math.Pow(26, xmlExcelColumnName.Length - index - 1);
            }

            if (value == xmlValue)
            {
                return true;
            }
            return false;
        }

        private string GetMappingValue(FieldTplt field, string key)
        {
            if (s_mappingValues.ContainsKey(field.TableColumn))
            {
                DataTable table = s_mappingValues[field.TableColumn];
                if (table != null)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        if (row[0].ToString() == key)
                        {
                            return row[1].ToString();
                        }
                    }
                }
            }
            else
            {
                string path = field.TypeData.Replace("~", AppDomain.CurrentDomain.BaseDirectory);

                ExcelUtil util = new ExcelUtil(path);
                try
                {
                    DataTable table = util.GetData(util.GetSheets()[0]);
                    s_mappingValues.Add(field.TableColumn, table);
                }
                catch
                {
                    s_mappingValues.Add(field.TableColumn, null);
                }
                finally
                {
                    util.Close();
                }
            }
            return key;
        }

        public static bool ExportExcel(DataTable table, string excelFileName)
        {
            object objOpt = System.Reflection.Missing.Value;
            Excel.Application excel = new Excel.Application();
            try
            {
                excel.Visible = false;
                Excel._Workbook wkb = excel.Workbooks.Add(objOpt);
                Excel._Worksheet wks = (Excel._Worksheet)wkb.ActiveSheet;

                wks.Visible = Excel.XlSheetVisibility.xlSheetVisible;

                int rowIndex = 1;
                int colIndex = 0;

                foreach (DataColumn col in table.Columns)
                {
                    colIndex++;
                    excel.Cells[1, colIndex] = col.ColumnName;
                }

                foreach (DataRow row in table.Rows)
                {
                    rowIndex++;
                    colIndex = 0;
                    foreach (DataColumn col in table.Columns)
                    {
                        colIndex++;
                        excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                    }
                }
                //excel.Sheets[0] = "sss";
                wkb.SaveAs(excelFileName, objOpt, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
                wkb.Close(false, objOpt, objOpt);
            }
            catch
            {
                return false;
            }
            finally
            {
                excel.Quit();
            }

            return true;
        }
    }

    public class AA24
    {
        /// <summary>
        /// 27->AA
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string ToAA(int value)
        {
            string AA = string.Empty;
            int d = value / 26;
            int remainder = value % 26;

            if (d == 1 && remainder == 0)
            {
                AA = ToA(26) + AA;
                d = 0;
            }
            else
            {
                AA = ToA(remainder) + AA;
            }

            while (d > 0)
            {
                remainder = d % 26;
                d = d / 26;
                AA = ToA(remainder) + AA;
            }
            return AA;
        }

        /// <summary>
        /// 27->F27
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string ToF27(int value)
        {
            return string.Format("F{0}", value);
        }

        /// <summary>
        /// AA->27
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static int FromAA(string value)
        {
            int V = 0;

            for (int index = 0; index < value.Length; index++)
            {
                int v = FromA(value[index]);
                V += v * (int)Math.Pow(v, index);
            }
            return V;
        }

        /// <summary>
        /// F27->27
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static int FromF27(string value)
        {
            return Convert.ToInt32(value.Replace("F", string.Empty));
        }

        private static string ToA(int value)
        {
            if (value <= 0)
            {
                return string.Empty;
            }
            return new string((char)('A' + (value - 1)), 1);
        }

        private static int FromA(char value)
        {
            return (int)value - (char)'A' + 1;
        }
    }
}
